package com.audi.box.adapter;

import java.util.ArrayList;

import com.audi.dto.BoxItem;
import com.audi.dto.DeviceItem;
import com.audi.dto.HistoryMemberItem;
import com.audi.dto.MemberItem;
import com.audi.dto.WifiItem;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * @author HuyPVA
 * 
 */
public class SQLiteAsusHelperMember extends SQLiteAsusHelperCommon {
    /**
     * @param context
     */
    public SQLiteAsusHelperMember(final Context context) {
        super(context);
    }
    
    // get list history member status Come HOme/L
    
    public ArrayList<HistoryMemberItem> getListMemberHomeAction() {
        Log.d("HuySQLite Log", "getListMemberHomeAction");
        ArrayList<HistoryMemberItem> listMember = new ArrayList<HistoryMemberItem>();
        String selectQuery = " SELECT " + KEY_HISTORY_USER_HOME_ID + ", "
                + KEY_HISTORY_USER_HOME_USER_ID + ", "
                + KEY_HISTORY_USER_HOME_TYPE + ", "
                + KEY_HISTORY_USER_HOME_DATETIME + " , " + KEY_USER_NAME
                + " , " + KEY_USER_ICON + " FROM " + TABLE_HISTORY_USER_HOME
                + " , " + TABLE_USER;
        selectQuery = selectQuery + " WHERE history_user_home."
                
                + KEY_HISTORY_USER_HOME_USER_ID + " = " + " user._user_id "
                + " order by " + TABLE_HISTORY_USER_HOME + "."
                + KEY_HISTORY_USER_HOME_DATETIME + " DESC ";
        try {
            SQLiteDatabase db = this.getReadableDatabase();
            
            Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
            
            // selectQuery_get_User_By_ID=selectQuery_get_User_By_ID+itemMember.getHistoryMemberMemberId();
            // SQLiteDatabase db2 = this.getReadableDatabase();
            // Cursor cursor_GetNameFromID =
            // db2.rawQuery(selectQuery_get_User_By_ID, null);
            try {
                if (cursorSqliteAsusHelper.moveToFirst()) {
                    do {
                        
                        HistoryMemberItem itemMember = new HistoryMemberItem();
                        itemMember.setHistoryMemberId(Integer
                                .parseInt(cursorSqliteAsusHelper.getString(0)));
                        
                        itemMember.setHistoryMemberMemberId(Integer
                                .parseInt(cursorSqliteAsusHelper.getString(1)));
                        
                        itemMember.setHistoryMemberType(Integer
                                .parseInt(cursorSqliteAsusHelper.getString(2)));
                        itemMember.setHistoryMemberDate(cursorSqliteAsusHelper
                                .getString(3));
                        
                        // ----------------
                        
                        itemMember.setHistoryMemberName(cursorSqliteAsusHelper
                                .getString(4));
                        itemMember.setHistoryMemberIcon(cursorSqliteAsusHelper
                                .getString(5));
                        
                        listMember.add(itemMember);
                        
                    } while (cursorSqliteAsusHelper.moveToNext());
                }
                return listMember;
            } finally {
                if (cursorSqliteAsusHelper != null) {
                    cursorSqliteAsusHelper.close();
                }
                if (db != null) {
                    db.close();
                }
            }
        } catch (Exception ex) {
            return listMember;
        }
    }
    
    /**
     * Add new MemberItem
     * 
     * @author CuongPH7
     * @param member
     *            MemberItem
     * @return Member id if successful, 0 if fail
     */
    public int addMember(MemberItem member) {
        Log.d("HuySQLite Log", "addMember");
        ContentValues values = convertMemberItemToContentValue(member);
        
        return sqlInsert(TABLE_USER, null, values);
    }
    
    // get last user NhanNLT
    public String getLastUserIcon(int id) {
        Log.d("HuySQLite Log", "getLastUserIcon");
        
        String selectQuery = " SELECT " + KEY_USER_ICON + " FROM " + TABLE_USER
                + " WHERE " + KEY_USER_ID + " = " + id;
        
        // selectQuery+= " ORDER BY "+ KEY_SCENE_ID+ " DESC";
        String tmp = "";
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    tmp = cursorSqliteAsusHelper.getString(0);
                    
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return tmp;
    }
    
    public int getLastUserStatus(int id) {
        Log.d("HuySQLite Log", "getLastUserStatus");
        
        String selectQuery = " SELECT " + KEY_HISTORY_USER_HOME_TYPE + " FROM "
                + TABLE_HISTORY_USER_HOME + " WHERE "
                + KEY_HISTORY_USER_HOME_USER_ID + " = " + id;
        
        // selectQuery+= " ORDER BY "+ KEY_SCENE_ID+ " DESC";
        int tmp = 0;
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    tmp = Integer.parseInt(cursorSqliteAsusHelper.getString(0));
                    
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return tmp;
    }
    
    /**
     * Check a Member Item exist
     * <p>
     * Modify 11/09
     * 
     * @author Unknow
     * @param member
     *            MemberItem
     * @return <code>True</code> if successful <code>False</code> if MemberItem
     *         does not Exist
     */
    public boolean checkExistMember(MemberItem member) {
        String selectQuery = "SELECT "
                + KEY_USER_ID
                + " FROM " + TABLE_USER
                + " WHERE "
                + KEY_USER_ID + " = " + member.getMemberId() + " AND "
                + KEY_USER_IS_DELETED + " =0";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor.getCount() > 0) {
            return true;
        }
        return false;
    }
    
    public int deleteMember(MemberItem memberItem) {
        Log.d("HuySQLite Log", "deleteMember");
        ContentValues values = new ContentValues();
        values.put(KEY_USER_IS_DELETED, 1);
        
        return sqlUpdate(TABLE_USER, values,
                KEY_USER_ID + "=" + String.valueOf(memberItem.getMemberId()),
                null);
    }
    
    /**
     * Update MemberItem
     * 
     * @param member
     *            MemberItem
     * @return <code>1</code> if successful <code>0</code> if MemberItem does
     *         not Exist
     */
    public int updateMember(MemberItem member) {
        Log.d("HuySQLite Log", "updateMember");
        
        ContentValues values = convertMemberItemToContentValue(member);
        
        return sqlUpdate(TABLE_USER, values,
                KEY_USER_ID + "=" + String.valueOf(member.getMemberId()),
                null);
    }
    
    /**
     * Get all MemberItem List.
     * 
     * @return an ArrayList MemberItem
     */
    public ArrayList<MemberItem> getListMember() {
        Log.d("HuySQLite Log", "getListMember");
        ArrayList<MemberItem> listmember = new ArrayList<MemberItem>();
        
        String selectQuery = "SELECT * FROM " + TABLE_USER;
        
        selectQuery += " WHERE " + TABLE_USER + "." + KEY_USER_IS_DELETED
                + " =0";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        try {
            if (cursor.moveToFirst()) {
                do {
                    MemberItem member = createMemberItem(cursor);
                    listmember.add(member);
                } while (cursor.moveToNext());
            }
            return listmember;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
    }
    
    /**
     * @deprecated {@link #addMember(MemberItem)}
     * @param item
     * @return <code>1</code> if successful <code>0</code> if MemberItem does
     *         not Exist
     */
    @Deprecated
    public int insertUser(MemberItem member) {
        Log.d("HuySQLite Log", "insertUser");
        return addMember(member);
    }
    
    /*
     * insert data WIfi for table wifi
     */
    public int insertWifi(WifiItem item) {
        Log.d("HuySQLite Log", "insertWifi");
        ContentValues values = new ContentValues();
        values.put(KEY_WIFI_CODE, item.getWifiCode());
        values.put(KEY_WIFI_NAME, item.getWifiName());
        values.put(KEY_WIFI_PASSWORD, item.getWifiPassword());
        values.put(KEY_WIFI_IS_DELETED, "0");
        
        return sqlInsert(TABLE_WIFI, null, values);
    }
    
    /*
     * insert data Box for table Box
     */
    public int insertBox(BoxItem item) {
        Log.d("HuySQLite Log", "insertBox");
        ContentValues values = new ContentValues();
        values.put(KEY_BOX_CODE, item.getBoxCode());
        values.put(KEY_BOX_WIFI_ID, item.getWifiId());
        values.put(KEY_BOX_NAME, item.getBoxName());
        values.put(KEY_BOX_PASSWORD, item.getBoxPassword());
        values.put(KEY_BOX_IS_DELETED, 0);
        
        return sqlInsert(TABLE_BOX, null, values);
    }
    
    public DeviceItem getUserByDeviceId(int placeId) {
        DeviceItem deviceItem = new DeviceItem();
        String selectQuery = "SELECT " + TABLE_DEVICE + "." + KEY_DEVICE_ID
                + ", " + TABLE_DEVICE + "." + KEY_DEVICE_NAME + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_USER_ID + ", " + TABLE_USER
                + "." + KEY_USER_NAME;
        
        selectQuery += " FROM " + TABLE_DEVICE + " LEFT OUTER JOIN "
                + TABLE_USER + " ON " + TABLE_DEVICE + "." + KEY_DEVICE_USER_ID
                + " = " + TABLE_USER + "." + KEY_USER_ID;
        
        selectQuery += " WHERE " + TABLE_DEVICE + "." + KEY_DEVICE_IS_DELETED
                + " = 0 ";
        selectQuery += " AND " + TABLE_DEVICE + "." + KEY_DEVICE_PLACE_ID
                + " = " + placeId;
        selectQuery += " GROUP BY " + TABLE_DEVICE + "." + KEY_DEVICE_ID;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        try {
            if (cursor.moveToFirst()) {
                do {
                    deviceItem
                            .setDeviceId(Integer.parseInt(cursor.getString(0)));
                    deviceItem.setDeviceName(cursor.getString(1));
                    deviceItem.setUserId(Integer.parseInt(cursor.getString(2)));
                    deviceItem.setUserName(cursor.getString(3));
                } while (cursor.moveToNext());
            }
            return deviceItem;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    /*
     * Add new Wifi
     */
    public void addWifi(WifiItem item) {
        Log.d("HuySQLite Log", "addWifi");
        ContentValues values = new ContentValues();
        values.put(KEY_WIFI_NAME, item.getWifiName());
        values.put(KEY_WIFI_PASSWORD, item.getWifiPassword());
        values.put(KEY_WIFI_CODE, item.getWifiCode());
        values.put(KEY_WIFI_IS_DELETED, 0);
        
        sqlInsert(TABLE_WIFI, null, values);
    }
    
    /*
     * get list wifi
     */
    public ArrayList<WifiItem> getListWifi() {
        Log.d("HuySQLite Log", "getListWifi");
        ArrayList<WifiItem> list = new ArrayList<WifiItem>();
        String selectQuery = "SELECT " + TABLE_WIFI + "." + KEY_WIFI_ID + ", "
                + TABLE_WIFI + "." + KEY_WIFI_NAME + ", " + TABLE_WIFI + "."
                + KEY_WIFI_PASSWORD + ", " + TABLE_WIFI + "." + KEY_WIFI_CODE
                + " FROM " + TABLE_WIFI;
        
        selectQuery += " WHERE " + TABLE_WIFI + "." + KEY_WIFI_IS_DELETED
                + " = 0 ";
        
        selectQuery += " ORDER BY " + TABLE_WIFI + "." + KEY_WIFI_ID + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    WifiItem item = new WifiItem();
                    item.setWifiId(Integer.parseInt(cursorSqliteAsusHelper
                            .getString(0)));
                    item.setWifiName(cursorSqliteAsusHelper.getString(1));
                    item.setWifiPassword(cursorSqliteAsusHelper.getString(2));
                    item.setWifiCode(cursorSqliteAsusHelper.getString(3));
                    list.add(item);
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            return list;
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    /*
     * Add new box
     */
    public void addBox(BoxItem item) {
        Log.d("HuySQLite Log", "addBox");
        ContentValues values = new ContentValues();
        values.put(KEY_BOX_NAME, item.getBoxName());
        values.put(KEY_BOX_PASSWORD, item.getBoxPassword());
        values.put(KEY_BOX_CODE, item.getBoxCode());
        values.put(KEY_BOX_WIFI_ID, item.getWifiId());
        values.put(KEY_BOX_IS_DELETED, 0);
        
        sqlInsert(TABLE_BOX, null, values);
    }
    
    public void editNamebox(BoxItem boxItem) {
        Log.d("HuySQLite Log", "editNamebox");
        ContentValues values = new ContentValues();
        values.put(KEY_BOX_NAME, boxItem.getBoxName());
        
        // values.put(KEY_BOX_ID, boxItem.getBoxId());
        sqlUpdate(TABLE_BOX, values,
                KEY_BOX_ID + " = " + String.valueOf(boxItem.getBoxId()), null);
        
    }
    
    /*
     * public ArrayList<PlaceItem> sumPlaceInRoot() { ArrayList<PlaceItem>
     * listPlace = new ArrayList<PlaceItem>(); String selectQuery = "SELECT "+
     * TABLE_WIFI+ "."+ KEY_WIFI_ID+ ", "+ TABLE_WIFI+ "."+ KEY_WIFI_NAME+ ", "+
     * TABLE_WIFI+ "."+ KEY_WIFI_PASSWORD+ ", "+ TABLE_WIFI+ "."+ KEY_WIFI_CODE+
     * " FROM "+ TABLE_WIFI; selectQuery+= " ORDER BY "+ KEY_SCENE_ID+ " DESC";
     * 
     * SQLiteDatabase db = this.getReadableDatabase(); Cursor
     * cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
     * 
     * try { if (cursorSqliteAsusHelper.moveToFirst()) { do { PlaceItem
     * placeItem = new PlaceItem(); placeItem.setSceneId(Integer
     * .parseInt(cursorSqliteAsusHelper.getString(0))); placeItem
     * .setSceneTitle(cursorSqliteAsusHelper.getString(1));
     * 
     * } while (cursorSqliteAsusHelper.moveToNext()); } return listPlace; }
     * finally { if (cursorSqliteAsusHelper != null) {
     * cursorSqliteAsusHelper.close(); } if (db != null) { db.close(); } }
     */
    /*
     * get list box
     */
    public ArrayList<BoxItem> getListBox(int wifiId) {
        Log.d("HuySQLite Log", "getListBox");
        ArrayList<BoxItem> list = new ArrayList<BoxItem>();
        String selectQuery = "SELECT " + TABLE_BOX + "." + KEY_BOX_ID + ", "
                + TABLE_BOX + "." + KEY_BOX_NAME + ", " + TABLE_BOX + "."
                + KEY_BOX_PASSWORD + ", " + TABLE_BOX + "." + KEY_BOX_CODE
                + "," + TABLE_BOX + "." + KEY_BOX_WIFI_ID + ", " + TABLE_WIFI
                + "." + KEY_WIFI_NAME + ", " + " FROM " + TABLE_BOX
                
                + " LEFT OUTER JOIN " + TABLE_WIFI + " ON " + TABLE_WIFI + "."
                + KEY_WIFI_ID + " = " + TABLE_BOX + "." + KEY_BOX_WIFI_ID;
        
        selectQuery += " WHERE " + TABLE_BOX + "." + KEY_BOX_IS_DELETED
                + " = 0 ";
        selectQuery += " AND " + TABLE_BOX + "." + KEY_BOX_WIFI_ID + " = "
                + wifiId;
        
        selectQuery += " ORDER BY " + TABLE_BOX + "." + KEY_BOX_ID + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    BoxItem item = new BoxItem();
                    item.setBoxId(Integer.parseInt(cursorSqliteAsusHelper
                            .getString(0)));
                    item.setBoxName(cursorSqliteAsusHelper.getString(1));
                    item.setBoxPassword(cursorSqliteAsusHelper.getString(2));
                    item.setBoxCode(cursorSqliteAsusHelper.getString(3));
                    item.setWifiId(Integer.parseInt(cursorSqliteAsusHelper
                            .getString(4)));
                    item.setWifiName(cursorSqliteAsusHelper.getString(5));
                    list.add(item);
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            return list;
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
}
